# -*- coding:utf-8 -*-
"""
@file: mst_sql01.py 
@time: 2019/07/21
@author: https://mp.weixin.qq.com/s/Tasi0ml7mF6hafI0EwCjnA
descript:一道SQL题的多种解法
店铺    销售日期    销售额
A    2017-10-11  300
A    2017-10-12  200
B    2017-10-11  400
B    2017-10-12  200
A    2017-10-13  100
A    2017-10-15  100
C    2017-10-11  350
C    2017-10-15  400
C    2017-10-16  200
D    2017-10-13  500
E    2017-10-14  600
E    2017-10-15  500
D    2017-10-14  600
B    2017-10-13  300
C    2017-10-17  100

需求：求出连续三天有销售记录的店铺
"""
import pymysql

# 连接本地数据库
# 链接mysql数据库
import tqdm

def insert_data01 (cursor,file_path):
	with open(file_path) as f:
		lines_list = f.readlines()
		# lines_list = tqdm.tqdm(f)
		for item in lines_list:
			print(item)
			item01 = item.replace("\n", '').split(' ')
			# insert into sales values('A', '2017-10-11', 300);
			sql03 = """
		insert into sales values ('{name}','{day}',{amount})
		""".format(name=item01[0], day=item01[4], amount=item01[6])
			print(sql03)
			cursor.execute(sql03)

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='yxl_test',
					 charset='utf8')
#创建指针/游标
cursor = db.cursor()
#判断表是否存在，不存在则创建
sql01 = """
drop table if exists sales
"""
cursor.execute(sql01)
sql02 = '''
create table sales (name char(1), day char(10), amount int)
'''
cursor.execute(sql02)





# 将data01.txt里面的数据插入到一张表中
insert_data01(cursor, "./data01.txt")
		
		
# 创建表
# 提交事务
db.commit()
# 关闭游标/指针
cursor.close()
# 关闭数据库
db.close()
